/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tces.controller;

import com.tces.model.Program;
import com.tces.model.Student;
import com.tces.utils.ConnectionDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import org.apache.log4j.Logger;

/**
 *
 * @author arnolda
 */
public class ProgramDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(ProgramDAO.class);

    public static List<Program> findAll() {
        List<Program> programList = new ArrayList<Program>();
        try {

            String sql = "SELECT\n"
                    + "c.college_id,\n"
                    + "c.college_code,\n"
                    + "c.college_name,\n"
                    + "p.program_id,\n"
                    + "p.program_code,\n"
                    + "p.program_description,\n"
                    + "p.program_status,\n"
                    + "pr.program_revision_id,\n"
                    + "pr.program_revision_name,\n"
                    + "pr.program_revision_status,\n"
                    + "CONCAT_WS(' - ',p.program_code,pr.program_revision_name) program_code_revision_name\n"
                    + "FROM\n"
                    + "programs AS p\n"
                    + "INNER JOIN program_revisions AS pr ON pr.program_id = p.program_id\n"
                    + "INNER JOIN colleges AS c ON p.college_id = c.college_id";

            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            //        preparedStatement.setInt(1, subjectOfferingID);
            log.info(preparedStatement.toString());
            ResultSet rs = preparedStatement.executeQuery();
            Program program;
            while (rs.next()) {
                program = new Program();
                program.setProgramID(rs.getInt("program_id"));
                program.setProgramCode(rs.getString("program_code"));
                program.setProgramDescription(rs.getString("program_description"));
                program.setProgramStatus(rs.getInt("program_status"));

                program.setCollegeID(rs.getInt("college_id"));
                program.setCollegeCode(rs.getString("college_code"));
                program.setCollegeName(rs.getString("college_name"));

                program.setProgramRevisionID(rs.getInt("program_revision_id"));
                program.setProgramRevisionName(rs.getString("program_revision_name"));
                program.setProgramRevisionStatus(rs.getInt("program_revision_status"));

                program.setProgramCodeRevisionName(rs.getString("program_code_revision_name"));
                programList.add(program);
            }
            closeConnection(getConnectionObject());

        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        return programList;
    }

    public static List<Program> findAll(int collegeID) {
        List<Program> programList = new ArrayList<Program>();
        try {

            String sql = "SELECT\n"
                    + "c.college_id,\n"
                    + "c.college_code,\n"
                    + "c.college_name,\n"
                    + "p.program_id,\n"
                    + "p.program_code,\n"
                    + "p.program_description,\n"
                    + "p.program_status,\n"
                    + "pr.program_revision_id,\n"
                    + "pr.program_revision_name,\n"
                    + "pr.program_revision_status,\n"
                    + "CONCAT_WS(' - ',p.program_code,pr.program_revision_name) program_code_revision_name\n"
                    + "FROM\n"
                    + "programs AS p\n"
                    + "INNER JOIN program_revisions AS pr ON pr.program_id = p.program_id\n"
                    + "INNER JOIN colleges AS c ON p.college_id = c.college_id\n"
                    + "WHERE c.college_id=?";

            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, collegeID);
            log.info(preparedStatement.toString());
            ResultSet rs = preparedStatement.executeQuery();
            Program program;
            while (rs.next()) {
                program = new Program();
                program.setProgramID(rs.getInt("program_id"));
                program.setProgramCode(rs.getString("program_code"));
                program.setProgramDescription(rs.getString("program_description"));
                program.setProgramStatus(rs.getInt("program_status"));

                program.setCollegeID(rs.getInt("college_id"));
                program.setCollegeCode(rs.getString("college_code"));
                program.setCollegeName(rs.getString("college_name"));

                program.setProgramRevisionID(rs.getInt("program_revision_id"));
                program.setProgramRevisionName(rs.getString("program_revision_name"));
                program.setProgramRevisionStatus(rs.getInt("program_revision_status"));

                program.setProgramCodeRevisionName(rs.getString("program_code_revision_name"));
                programList.add(program);
            }
            closeConnection(getConnectionObject());

        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        return programList;
    }
}
